﻿Module ProjectActivityDao

    Public Function searchDataByFilter(ByRef projectActivity As ProjectActivity) As Data.DataTable
        DataAccessConnection.openConnection()

        Dim command As New OracleClient.OracleCommand
        Dim table As New Data.DataTable
        Dim activity As New Activity

        Try
            Dim query As String = ""

            query = "SELECT pa.id                   pa_id, "
            query &= "       pa.estimated_start_date pa_estimated_start_date, "
            query &= "       pa.estimated_end_date pa_estimated_end_date, "
            query &= "       pa.start_date           pa_start_date, "
            query &= "       pa.end_date             pa_end_date, "
            query &= "       pa.project_id           pa_project_id, "
            query &= "       pa.activity_id          pa_activity_id, "
            query &= "       a.id                    a_id, "
            query &= "       a.name                  a_name, "
            query &= "       a.description           a_description "
            query &= "  from " & projectActivity.getTableName & " pa, " & activity.getTableName & " a "
            query &= " where (pa.project_id = :project_id OR 0 = :project_id) "
            query &= "   and (a.id = :id OR 0 = :id)  "
            query &= "   and (pa.activity_id = a.id) "

            command.Connection = connection
            command.CommandType = CommandType.Text
            command.CommandText = query
            command.Parameters.AddWithValue("project_id", projectActivity.projectProperty.idProperty)
            command.Parameters.AddWithValue("id", If(projectActivity.activityProperty Is Nothing, Nothing, projectActivity.activityProperty.idProperty))

            table.Load(command.ExecuteReader)
        Catch
            DataAccessConnection.closeConnection()
        End Try

        DataAccessConnection.closeConnection()

        Return table
    End Function

    Public Function searchData(ByRef projectActivity As ProjectActivity) As ArrayList
        Dim table As New Data.DataTable
        Dim list As New ArrayList
        table = searchDataByFilter(projectActivity)
        list = convertTableToList(table)
        Return list
    End Function


    Private Function convertTableToList(ByRef table As Data.DataTable) As ArrayList
        Dim listProjectActivities As New ArrayList

        For Each row In table.Rows
            Dim projectActivity As New ProjectActivity
            projectActivity.idProperty = row("pa_id")
            projectActivity.estimatedFromDateProperty = row("pa_estimated_start_date")
            projectActivity.estimatedToDateProperty = row("pa_estimated_end_date")
            projectActivity.realFromDateProperty = IIf(IsDBNull(row("pa_start_date")), Nothing, row("pa_start_date"))
            projectActivity.realToDateProperty = IIf(IsDBNull(row("pa_end_date")), Nothing, row("pa_end_date"))

            Dim project As New Project
            project.idProperty = row("pa_project_id")
            projectActivity.projectProperty = project

            Dim activity As New Activity
            activity.idProperty = row("a_id")
            activity.nameProperty = row("a_name")
            activity.descriptionProperty = IIf(IsDBNull(row("a_description")), Nothing, row("a_description"))
            projectActivity.activityProperty = activity

            listProjectActivities.Add(projectActivity)
        Next


        Return listProjectActivities
    End Function
End Module
